insert overwrite table jms_dws.dws_order_hour_cnt_dt partition(dt)
    select
         dt                                                    as input_date
        ,hour(input_time)                                      as input_hour
        ,order_source_code                                     as order_source_code
        ,max(order_source_name)                                as order_source_name
        ,pick_network_code                                     as pick_network_code
        ,max(pick_network_name)                                as pick_network_name
        ,max(pick_franchisee_code)                             as pick_franchisee_code
        ,max(pick_franchisee_name)                             as pick_franchisee_name
        ,max(pick_agent_code)                                  as pick_agent_code
        ,max(pick_agent_name)                                  as pick_agent_name
        ,sender_province_id                                    as sender_province_id
        ,sender_province_name                                  as sender_province_name
        ,sender_city_id                                        as sender_city_id
        ,sender_city_name                                      as sender_city_name
        ,sender_area_id                                        as sender_area_id
        ,sender_area_name                                      as sender_area_name
        ,sum(if(is_delete=2,1,0))                              as delete_cnt
        ,sum(if(order_status_code=104,1,0))                    as cancel_cnt
        ,sum(if(is_delete=1 and order_status_code<>104,1,0))   as normal_cnt
        ,sum(1)                                                as all_cnt
        ,dt
from jms_dwd.dwd_order_detail_dt
where dt = '{{ execution_date | cst_ds }}'
group by
        dt
       ,hour(input_time)
       ,order_source_code
       ,pick_network_code
       ,sender_province_id
       ,sender_province_name
       ,sender_city_id
       ,sender_city_name
       ,sender_area_id
       ,sender_area_name
   distribute by 1 ;